﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DAL.Entity;
using DAL.Persistencia;

namespace DAL.Persistencia
{
    public class RangeDal
    {

        private NewTierraEntities Con;

        public RangeDal()
        {
            Con = new NewTierraEntities();
        }

        public void Salvar(Ranges r)
        {
            try
            {
                Con.AddToRanges(r);
                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodos(int IdQuote)
        {
            try
            {
                return (from rg in Con.Ranges
                        orderby rg.Ordem, rg.Ranges_de
                        where rg.Quotation_Id == IdQuote
                        select rg).ToList();

                //return Con.Ranges.Where(r => r.Quotation_Id == IdQuote).OrderBy(r => r.Ordem && r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodos(int IdQuote, int OptQuote)
        {
            try
            {
                return (from rg in Con.Ranges
                        orderby rg.Ordem, rg.Ranges_de
                        where rg.Quotation_Id == IdQuote &&
                              rg.OptQuote == OptQuote
                        select rg).ToList();

                //return Con.Ranges.Where(r => r.Quotation_Id == IdQuote).OrderBy(r => r.Ordem && r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodos(int IdQuote, int OptQuote, int IdTabela, string flag)
        {
            try
            {
                return Con.Ranges.Where(q => q.Quotation_Id == IdQuote &&
                                             q.FileTabelaId == IdTabela &&
                                             q.OptQuote == OptQuote &&
                                             q.Flag.Equals(flag))
                                             .OrderBy(q => q.Ordem)
                                             .ThenBy(q => q.Ranges_de).ToList();
            }
            catch
            {

                throw;
            }
        }

        public List<Ranges> ListarTodosOrdenado(int IdQuote)
        {
            try
            {
                return (from rg in Con.Ranges
                        orderby rg.Ordem, rg.Ranges_de, rg.RangeID, rg.DAY
                        where rg.Quotation_Id == IdQuote
                        select rg).ToList();

                //return Con.Ranges.Where(r => r.Quotation_Id == IdQuote).OrderBy(r => r.Ordem && r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosOrdem(int IdQuote)
        {
            try
            {
                return (from rg in Con.Ranges
                        orderby rg.Ordem, rg.Ranges_de
                        where rg.Quotation_Id == IdQuote
                        select rg).ToList();

                //return Con.Ranges.Where(r => r.Quotation_Id == IdQuote).OrderBy(r => r.Ordem && r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object ListarTodosOrdemDist(int IdQuote)
        {
            try
            {

                return (from rg in Con.Ranges
                        where rg.Quotation_Id == IdQuote
                        select new
                        {
                            rg.Ranges_de,
                            rg.Ranges_ate,
                            rg.Ordem,
                            rg.Quotation_Id
                        }).Distinct().OrderBy(r => r.Ordem).ThenBy(r => r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public object ListarTodosOrdemDist(int IdQuote, int optQuote)
        {
            try
            {

                return (from rg in Con.Ranges
                        where rg.Quotation_Id == IdQuote &&
                              rg.OptQuote == optQuote
                        select new
                        {
                            rg.Ranges_de,
                            rg.Ranges_ate,
                            rg.Ordem,
                            rg.Quotation_Id
                        }).Distinct().OrderBy(r => r.Ordem).ThenBy(r => r.Ranges_de).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosGroup(int IdQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuote).GroupBy(x => x.Ranges_de).Select(y => y.FirstOrDefault()).OrderBy(r => r.Ordem).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosGroup(int IdQuote, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuote && r.OptQuote == optQuote).GroupBy(x => x.Ranges_de).Select(y => y.FirstOrDefault()).OrderBy(r => r.Ordem).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosGroupNotTC(int IdQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuote && !r.Ranges_de.Equals("TC")).GroupBy(x => x.Ranges_de).Select(y => y.FirstOrDefault()).OrderBy(r => r.Ordem).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosGroupNotTC(int IdQuote, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuote && !r.Ranges_de.Equals("TC") && r.OptQuote == optQuote).GroupBy(x => x.Ranges_de).Select(y => y.FirstOrDefault()).OrderBy(r => r.Ordem).ToList();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorId(int IdRange)
        {
            try
            {
                return Con.Ranges.Where(r => r.Ranges_id == IdRange).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorIdID(int RangeID)
        {
            try
            {
                return Con.Ranges.Where(r => r.RangeID == RangeID).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifa(int IdTarifa, int IdQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa &&
                                             r.Quotation_Id == IdQuote).ToList();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdTarifa)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdTarifa, string de, string ate, string flag)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Flag.Equals(flag)).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdTarifa, string de, string ate)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate)).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdTarifa, string de, string ate, int IdQuot, int OptQuote, string flag)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Quotation_Id == IdQuot &&
                                             r.OptQuote == OptQuote &&
                                             r.Flag.Equals(flag)).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaOne(int IdQuot, string de, string ate)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate)).FirstOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaOne(int IdQuot)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot).FirstOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifaDeAte(int IdQuot, string de, string ate)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate)).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifaDeAteOpt(int IdQuot, string de, string ate, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.OptQuote == optQuote).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifaDeAteOptItens(int IdQuot, int optQuote, int IdTabela, string type)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Flag.Equals(type) &&
                                             r.FileTabelaId == IdTabela &&
                                             r.OptQuote == optQuote).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifaDeAteOptItensOrdem(int IdQuot, int optQuote, int IdTabela, string type)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Flag.Equals(type) &&
                                             r.FileTabelaId == IdTabela &&
                                             r.OptQuote == optQuote)
                                             .OrderBy(r => r.Ordem)
                                             .ThenBy(r => r.Ranges_de)
                                             .ToList();
            }
            catch
            {
                throw;
            }
        }
       
        public List<Ranges> ObterPorTarifaDeAteOrdem(int IdQuot, string de, string ate, int ordem)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Ordem == ordem).ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ObterPorTarifaDeAteOrdem(int IdQuot, string de, string ate, int ordem, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Ordem == ordem &&
                                             r.OptQuote == optQuote).ToList();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaDeAte(int IdQuot, string de, string ate, int top)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate)).Skip(top).Take(top).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaOptQuote(int IdQuot, int IdTarifa, string de, string ate, string flag, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Flag.Equals(flag) &&
                                             r.RangeID == null &&
                                             r.OptQuote == optQuote).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdQuot, int IdTarifa, string de, string ate, string flag)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Flag.Equals(flag) &&
                                             r.RangeID == null).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdQuot, int IdTarifa, string de, string ate, string flag, int RangeID)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Flag.Equals(flag) &&
                                             r.RangeID == RangeID).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifa(int IdQuot, int IdTarifa, string de, string ate, string flag, int RangeID, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.FileTabelaId == IdTarifa &&
                                             r.Ranges_de.Equals(de) &&
                                             r.Ranges_ate.Equals(ate) &&
                                             r.Flag.Equals(flag) &&
                                             r.RangeID == RangeID &&
                                             r.OptQuote == optQuote).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaLast(int IdTarifa)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa).
                                        OrderByDescending(r => r.Ranges_id).Take(1).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public Ranges ObterPorTarifaLast(int IdTarifa, string flag)
        {
            try
            {
                return Con.Ranges.Where(r => r.FileTabelaId == IdTarifa &&
                                             r.Flag.Equals(flag)).
                                        OrderByDescending(r => r.Ranges_id).Take(1).SingleOrDefault();
            }
            catch
            {
                throw;
            }
        }

        public void AtualizarValores(Ranges novo)
        {
            try
            {
                Ranges antigo = ObterPorId(novo.Ranges_id);

                antigo.Ranges_id = novo.Ranges_id;
                antigo.VendaNet = novo.VendaNet;
                antigo.Venda = novo.Venda;
                antigo.ValorTotal = novo.ValorTotal;
                antigo.Valor = novo.Valor;
                antigo.markup = novo.markup;
                antigo.markupNet = novo.markupNet;
                antigo.desconto = novo.desconto;
                antigo.descontoNet = novo.descontoNet;

                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public void Excluir(Ranges r)
        {
            try
            {
                Con.DeleteObject(r);
                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public void AtualizaOrdem(Ranges novo)
        {
            try
            {
                Ranges antigo = ObterPorId(novo.Ranges_id);

                antigo.Ordem = novo.Ordem;

                Con.SaveChanges();
            }
            catch
            {
                throw;
            }
        }

        public int QtdBases(int IdQuot)
        {
            try
            {
                return (from rgs in Con.Ranges
                        where rgs.Quotation_Id == IdQuot
                        orderby rgs.Ordem, rgs.Ranges_de
                        select new
                        {
                            rgs.Ordem,
                            rgs.Ranges_de,
                            rgs.Ranges_ate
                        }).Distinct().Count();
            }
            catch
            {
                throw;
            }
        }

        public int QtdBases(int IdQuot, int optQuote)
        {
            try
            {
                return (from rgs in Con.Ranges
                        where rgs.Quotation_Id == IdQuot &&
                              rgs.OptQuote == optQuote
                        orderby rgs.Ordem, rgs.Ranges_de
                        select new
                        {
                            rgs.Ordem,
                            rgs.Ranges_de,
                            rgs.Ranges_ate
                        }).Distinct().Count();
            }
            catch
            {
                throw;
            }
        }

        public int QtdBasesTC(int IdQuot)
        {
            try
            {

                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals("TC")).Select(r => r.Ordem).Distinct().Count();

            }
            catch
            {
                throw;
            }
        }

        public int QtdBasesTC(int IdQuot, int OptBases)
        {
            try
            {

                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals("TC") &&
                                             r.OptQuote == OptBases).Select(r => r.Ordem).Distinct().Count();

            }
            catch
            {
                throw;
            }
        }

        public int QtdBases(int IdQuot, string De, string Ate)
        {
            try
            {

                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.Ranges_de.Equals(De) &&
                                             r.Ranges_ate.Equals(Ate)).Count();

            }
            catch
            {
                throw;
            }
        }

        public void AtualizarDAY(Ranges novo)
        {
            try
            {
                Ranges antigo = ObterPorId(novo.Ranges_id);

                antigo.DAY = novo.DAY;

                Con.SaveChanges();

            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarParaTC(int QuotId, int Ordem, string De)
        {
            try
            {

                return Con.Ranges.Where(r => r.Quotation_Id == QuotId &&
                                             r.Ordem == Ordem &&
                                             r.Ranges_de.Equals(De)).ToList();

            }
            catch
            {
                throw;
            }
        }

        public bool VerificaSeTC(int IdQuot, string De)
        {
            SqlCon c = new SqlCon();

            try
            {
                c.AbrirConexao();

                string sql = "SELECT TOP (1) Ranges_id FROM Ranges WHERE (Quotation_Id = @p1) AND (RangeID = ";
                sql = sql + "(SELECT TOP (1) Ranges_id FROM Ranges AS Ranges_1 WHERE (Quotation_Id = @p2) AND (Ranges_de = @p3))) ";
                sql = sql + "ORDER BY Ordem, Ranges_de ";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p3", De);

                c.Dr = c.Cmd.ExecuteReader();

                if (c.Dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        public bool VerificaSeTC(int IdQuot, string De, int optQuote)
        {
            SqlCon c = new SqlCon();

            try
            {
                c.AbrirConexao();

                string sql = "SELECT TOP (1) Ranges_id FROM Ranges WHERE (Quotation_Id = @p1) AND (RangeID = ";
                sql = sql + "(SELECT TOP (1) Ranges_id FROM Ranges AS Ranges_1 WHERE (Quotation_Id = @p2) AND (Ranges_de = @p3))) ";
                sql = sql + "ORDER BY Ordem, Ranges_de ";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p3", De);

                c.Dr = c.Cmd.ExecuteReader();

                if (c.Dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        public List<Ranges> RetornaListaTCPBase(int IdQuot, string De)
        {
            SqlCon c = new SqlCon();
            try
            {

                c.AbrirConexao();

                string sql = "SELECT Ranges_id, Ranges_de, Ranges_ate, Quotation_Id, Flag, FileTabelaId, markup, desconto, markupNet, descontoNet, VendaNet, Valor, Venda, ValorTotal, Ordem, ";
                sql = sql + "RangeID, DAY FROM Ranges WHERE (Quotation_Id = @p1) AND (Ranges_de = 'TC') AND (RangeID IN ";
                sql = sql + "(SELECT Ranges_id FROM Ranges AS Ranges_1 ";
                sql = sql + "WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2)))";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", De);

                c.Dr = c.Cmd.ExecuteReader();

                List<Ranges> lista = new List<Ranges>();

                while (c.Dr.Read())
                {
                    Ranges r = new Ranges();

                    r.Ranges_id = Convert.ToInt32(c.Dr["Ranges_id"]);
                    r.Ranges_de = c.Dr["Ranges_de"].ToString();
                    r.Ranges_ate = c.Dr["Ranges_ate"].ToString();
                    r.Quotation_Id = Convert.ToInt32(c.Dr["Quotation_Id"]);
                    r.Flag = c.Dr["Flag"].ToString();
                    r.FileTabelaId = Convert.ToInt32(c.Dr["FileTabelaId"]);
                    r.markup = Convert.ToDecimal(c.Dr["markup"]);
                    r.desconto = Convert.ToDecimal(c.Dr["desconto"]);
                    r.markupNet = Convert.ToDecimal(c.Dr["markupNet"]);
                    r.descontoNet = Convert.ToDecimal(c.Dr["descontoNet"]);
                    r.VendaNet = Convert.ToDecimal(c.Dr["VendaNet"]);
                    r.Valor = Convert.ToDecimal(c.Dr["Valor"]);
                    r.Venda = Convert.ToDecimal(c.Dr["Venda"]);
                    r.ValorTotal = Convert.ToDecimal(c.Dr["ValorTotal"]);
                    r.Ordem = Convert.ToInt32(c.Dr["Ordem"]);
                    r.RangeID = Convert.ToInt32(c.Dr["RangeID"]);
                    if (c.Dr["DAY"] == DBNull.Value) r.DAY = null; else r.DAY = Convert.ToInt32(c.Dr["DAY"]);

                    lista.Add(r);
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        public List<Ranges> RetornaListaTCPBase(int IdQuot, string De, int optQuote)
        {
            SqlCon c = new SqlCon();
            try
            {

                c.AbrirConexao();

                string sql = "SELECT Ranges_id, Ranges_de, Ranges_ate, Quotation_Id, Flag, FileTabelaId, markup, desconto, markupNet, descontoNet, VendaNet, Valor, Venda, ValorTotal, Ordem, ";
                sql = sql + "RangeID, DAY FROM Ranges WHERE (Quotation_Id = @p1) AND (Ranges_de = 'TC') AND (RangeID IN ";
                sql = sql + "(SELECT Ranges_id FROM Ranges AS Ranges_1 ";
                sql = sql + "WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2))) AND (OptQuote = @p3)";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", De);
                c.Cmd.Parameters.AddWithValue("@p3", optQuote);

                c.Dr = c.Cmd.ExecuteReader();

                List<Ranges> lista = new List<Ranges>();

                while (c.Dr.Read())
                {
                    Ranges r = new Ranges();

                    r.Ranges_id = Convert.ToInt32(c.Dr["Ranges_id"]);
                    r.Ranges_de = c.Dr["Ranges_de"].ToString();
                    r.Ranges_ate = c.Dr["Ranges_ate"].ToString();
                    r.Quotation_Id = Convert.ToInt32(c.Dr["Quotation_Id"]);
                    r.Flag = c.Dr["Flag"].ToString();
                    r.FileTabelaId = Convert.ToInt32(c.Dr["FileTabelaId"]);
                    r.markup = Convert.ToDecimal(BrancoNulo(c.Dr["markup"]));
                    r.desconto = Convert.ToDecimal(BrancoNulo(c.Dr["desconto"]));
                    r.markupNet = Convert.ToDecimal(BrancoNulo(c.Dr["markupNet"]));
                    r.descontoNet = Convert.ToDecimal(BrancoNulo(c.Dr["descontoNet"]));
                    r.VendaNet = Convert.ToDecimal(BrancoNulo(c.Dr["VendaNet"]));
                    r.Valor = Convert.ToDecimal(BrancoNulo(c.Dr["Valor"]));
                    r.Venda = Convert.ToDecimal(BrancoNulo(c.Dr["Venda"]));
                    r.ValorTotal = Convert.ToDecimal(BrancoNulo(c.Dr["ValorTotal"]));
                    r.Ordem = Convert.ToInt32(BrancoNulo(c.Dr["Ordem"]));
                    r.RangeID = Convert.ToInt32(BrancoNulo(c.Dr["RangeID"]));
                    if (c.Dr["DAY"] == DBNull.Value) r.DAY = null; else r.DAY = Convert.ToInt32(c.Dr["DAY"]);

                    lista.Add(r);
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        protected object BrancoNulo(object texto)
        {
            if (texto == DBNull.Value)
            {
                return "0";
            }
            else
            {
                return texto;
            }

        }

        public List<Ranges> RetornaListaNoPBase(int IdQuot, string De)
        {
            SqlCon c = new SqlCon();
            try
            {

                c.AbrirConexao();

                string sql = "SELECT Ranges_id, Ranges_de, Ranges_ate, Quotation_Id, Flag, FileTabelaId, markup, desconto, markupNet, descontoNet, VendaNet, Valor, Venda, ValorTotal, Ordem, ";
                sql = sql + "RangeID, DAY FROM Ranges WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2) AND (RangeID IN ";
                sql = sql + "(SELECT Ranges_id FROM Ranges AS Ranges_1 ";
                sql = sql + "WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2)))";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", De);

                c.Dr = c.Cmd.ExecuteReader();

                List<Ranges> lista = new List<Ranges>();

                while (c.Dr.Read())
                {
                    Ranges r = new Ranges();

                    r.Ranges_id = Convert.ToInt32(c.Dr["Ranges_id"]);
                    r.Ranges_de = c.Dr["Ranges_de"].ToString();
                    r.Ranges_ate = c.Dr["Ranges_ate"].ToString();
                    r.Quotation_Id = Convert.ToInt32(c.Dr["Quotation_Id"]);
                    r.Flag = c.Dr["Flag"].ToString();
                    r.FileTabelaId = Convert.ToInt32(c.Dr["FileTabelaId"]);
                    r.markup = Convert.ToDecimal(c.Dr["markup"]);
                    r.desconto = Convert.ToDecimal(c.Dr["desconto"]);
                    r.markupNet = Convert.ToDecimal(c.Dr["markupNet"]);
                    r.descontoNet = Convert.ToDecimal(c.Dr["descontoNet"]);
                    r.VendaNet = Convert.ToDecimal(c.Dr["VendaNet"]);
                    r.Valor = Convert.ToDecimal(c.Dr["Valor"]);
                    r.Venda = Convert.ToDecimal(c.Dr["Venda"]);
                    r.ValorTotal = Convert.ToDecimal(c.Dr["ValorTotal"]);
                    r.Ordem = Convert.ToInt32(c.Dr["Ordem"]);
                    r.RangeID = Convert.ToInt32(c.Dr["RangeID"]);
                    r.DAY = Convert.ToInt32(c.Dr["DAY"]);

                    lista.Add(r);
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        public List<Ranges> RetornaListaNoPBase(int IdQuot, string De, int optQuote)
        {
            SqlCon c = new SqlCon();
            try
            {

                c.AbrirConexao();

                string sql = "SELECT Ranges_id, Ranges_de, Ranges_ate, Quotation_Id, Flag, FileTabelaId, markup, desconto, markupNet, descontoNet, VendaNet, Valor, Venda, ValorTotal, Ordem, ";
                sql = sql + "RangeID, DAY FROM Ranges WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2) AND (RangeID IN ";
                sql = sql + "(SELECT Ranges_id FROM Ranges AS Ranges_1 ";
                sql = sql + "WHERE (Quotation_Id = @p1) AND (Ranges_de = @p2))) AND (OptQuote = @p3)";

                c.Cmd = new SqlCommand(sql, c.Con);

                c.Cmd.Parameters.AddWithValue("@p1", IdQuot);
                c.Cmd.Parameters.AddWithValue("@p2", De);
                c.Cmd.Parameters.AddWithValue("@p3", optQuote);

                c.Dr = c.Cmd.ExecuteReader();

                List<Ranges> lista = new List<Ranges>();

                while (c.Dr.Read())
                {
                    Ranges r = new Ranges();

                    r.Ranges_id = Convert.ToInt32(c.Dr["Ranges_id"]);
                    r.Ranges_de = c.Dr["Ranges_de"].ToString();
                    r.Ranges_ate = c.Dr["Ranges_ate"].ToString();
                    r.Quotation_Id = Convert.ToInt32(c.Dr["Quotation_Id"]);
                    r.Flag = c.Dr["Flag"].ToString();
                    r.FileTabelaId = Convert.ToInt32(c.Dr["FileTabelaId"]);
                    r.markup = Convert.ToDecimal(c.Dr["markup"]);
                    r.desconto = Convert.ToDecimal(c.Dr["desconto"]);
                    r.markupNet = Convert.ToDecimal(c.Dr["markupNet"]);
                    r.descontoNet = Convert.ToDecimal(c.Dr["descontoNet"]);
                    r.VendaNet = Convert.ToDecimal(c.Dr["VendaNet"]);
                    r.Valor = Convert.ToDecimal(c.Dr["Valor"]);
                    r.Venda = Convert.ToDecimal(c.Dr["Venda"]);
                    r.ValorTotal = Convert.ToDecimal(c.Dr["ValorTotal"]);
                    r.Ordem = Convert.ToInt32(c.Dr["Ordem"]);
                    r.RangeID = Convert.ToInt32(c.Dr["RangeID"]);
                    r.DAY = Convert.ToInt32(c.Dr["DAY"]);

                    lista.Add(r);
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                c.FecharConexao();
            }
        }

        public bool VerificaExisteTC(int IdQuot)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.RangeID != null).Count() != 0;
            }
            catch
            {
                throw;
            }
        }

        public bool VerificaExisteTC(int IdQuot, int optQuote)
        {
            try
            {
                return Con.Ranges.Where(r => r.Quotation_Id == IdQuot &&
                                             r.OptQuote == optQuote &&
                                             r.RangeID != null).Count() != 0;
            }
            catch
            {
                throw;
            }
        }

        public bool VerificaExisteRegOpt(int IdQuot, int OptQuote)
        {
            try
            {

                return ((from fca in Con.File_Carrinho
                         join fta in Con.File_Tarifas on fca.File_id equals fta.File_id
                         join rga in Con.Ranges on fta.File_Tarifas_id equals rga.FileTabelaId
                         where rga.Quotation_Id == IdQuot &&
                               rga.Flag.Equals("hotel") &&
                               fta.OptQuote == OptQuote
                         select new
                         {
                             Nome = fta.S_nome
                         }).Union
                              (from fca in Con.File_Carrinho
                               join fta in Con.File_Transfers on fca.File_id equals fta.File_id
                               join rga in Con.Ranges on fta.File_Transf_id equals rga.FileTabelaId
                               where rga.Quotation_Id == IdQuot &&
                                     rga.Flag.Equals("servico") &&
                                     fta.OptQuote == OptQuote
                               select new
                               {
                                   Nome = fta.S_nome
                               }).Union
                              (from fca in Con.File_Carrinho
                               join fta in Con.File_ServExtra on fca.File_id equals fta.File_id
                               join rga in Con.Ranges on fta.File_ServExtra_id equals rga.FileTabelaId
                               where rga.Quotation_Id == IdQuot &&
                                     rga.Flag.Equals("extra") &&
                                     fta.OptQuote == OptQuote
                               select new
                               {
                                   Nome = fta.S_nome
                               }).Count() != 0);

            }
            catch
            {
                throw;
            }
        }

        public Array RetornaOpts(int IdQuot)
        {
            try
            {

                return Con.Ranges.Where(q => q.Quotation_Id == IdQuot).Select(q => q.OptQuote).Distinct().ToArray();
                
            }
            catch 
            {                
                throw;
            }
        }       

        public object RetornaOptsFull(int IdQuot)
        {
            try
            {

                return (from rg in Con.Ranges
                        where rg.Quotation_Id == IdQuot
                        select new
                        {
                            rg.OptQuote,
                            rg.OptQuoteNome
                        }).Distinct().ToList();
            }
            catch
            {
                throw;
            }
        }

        public List<Ranges> ListarTodosOpts(int IdQuote, int Opt)
        {
            try
            {

                return Con.Ranges.Where(r => r.Quotation_Id == IdQuote &&
                                             r.OptQuote == Opt).ToList();

            }
            catch 
            {                
                throw;
            }
        }

        public void AtualizaListOpts(List<Ranges> r)
        {
            try
            {

                foreach (Ranges item in r)
                {
                    Ranges rr = ObterPorId(item.Ranges_id);
                    rr.OptQuoteNome = item.OptQuoteNome;                    
                    Con.SaveChanges();
                }

            }
            catch 
            {                
                throw;
            }
        }

        public List<string> RetornaTodasBases(int IdQuote, int OptQuote)
        {
            SqlCon con = new SqlCon();
            try
            {
                string sql = "SELECT DISTINCT Ranges_de, Ranges_ate, Ordem ";
                sql = sql + "FROM Ranges WHERE (Quotation_Id = @p1) AND (OptQuote = @p2) ";
                sql = sql + "ORDER BY Ordem, Ranges_de";

                con.AbrirConexao();

                con.Cmd = new SqlCommand(sql, con.Con);
                con.Cmd.Parameters.AddWithValue("@p1", IdQuote);
                con.Cmd.Parameters.AddWithValue("@p2", OptQuote);

                con.Dr = con.Cmd.ExecuteReader();

                List<string> lista = new List<string>();

                while (con.Dr.Read())
                {
                    lista.Add(con.Dr["Ranges_de"].ToString() + "/" + con.Dr["Ranges_ate"].ToString());
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                con.FecharConexao();
            }

        }

        public List<string> RetornaTodasBasesTC_Depois(int IdQuote, int OptQuote)
        {
            SqlCon con = new SqlCon();
            try
            {
                string sql = "SELECT DISTINCT Ranges_de, Ranges_ate, Ordem ";
                sql = sql + "FROM Ranges WHERE (Quotation_Id = @p1) AND (OptQuote = @p2) ";
                sql = sql + "ORDER BY  Ranges_de";

                con.AbrirConexao();

                con.Cmd = new SqlCommand(sql, con.Con);
                con.Cmd.Parameters.AddWithValue("@p1", IdQuote);
                con.Cmd.Parameters.AddWithValue("@p2", OptQuote);

                con.Dr = con.Cmd.ExecuteReader();

                List<string> lista = new List<string>();

                while (con.Dr.Read())
                {
                    lista.Add(con.Dr["Ranges_de"].ToString() + "/" + con.Dr["Ranges_ate"].ToString());
                }

                return lista;

            }
            catch
            {
                throw;
            }
            finally
            {
                con.FecharConexao();
            }

        }

    }
}
